﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using RLZY.others;
using Infragistics.Win.UltraWinGrid;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
namespace RLZY
{
    public partial class FormSJTJB : Form
    {
        public FormSJTJB()
        {
            InitializeComponent();
            
        }
        //public FormBDX_Modify(string username,String 权限)
        //{
        //    InitializeComponent();
        //    this.userID= strTextBox1Text;
        //}
        private static FormSJTJB childFromInstanc;
        static log4net.ILog LOG = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
        public String userID;
        public String 权限;


        public static FormSJTJB ChildFromInstanc
        {
            get
            {
                if (childFromInstanc == null || childFromInstanc.IsDisposed)
                //判断childFromInstanc是否有值，不为null或者窗体释放（当你将子窗体关了的时候）为childFromInstanc赋值
                {
                    childFromInstanc = new FormSJTJB();
                }
                return childFromInstanc;
                //返回的childFromInstanc（子窗体存在时返回的是null,关闭了（IsDisposed为true）或者第一次打开子窗体返回一个ChildForm实例）
            }
        }
        public void Last_Date()
        {
            others.myGZ mygz = new myGZ();
            DataSet dataSet = mygz.GetLastDate();

            bool flag = dataSet.Tables[0].Rows.Count > 0;
            if (flag)
            {
                this.LastDate = dataSet.Tables[0].Rows[0]["日期"].ToString();
            }
            else
            {
                this.LastDate = "9998-12-31";
            }

        }

        private void FormNotKey_GDX_Modify_Load(object sender, EventArgs e)
        {
            
        }

     

        private void BindData(DataSet ds)
        {
            DataView dv = new DataView();
            dv.Table = ds.Tables[0];
            //dv.Sort = "ID desc";//反排序
            //bindingSource1.DataSource = dv;
            this.ultraGrid1.DataSource = dv;
        }
        //列选择器
        private void btnCustomColumnChooser_Click(object sender, EventArgs e)
        {
            try
            {
                this.ShowCustomColumnChooserDialog();
            }
            catch (Exception ex) { LOG.Error(ex); }
        }
        private RLZY.CustomColumnChooser customColumnChooserDialog = null;
        
        private void ShowCustomColumnChooserDialog()
        {
            if (null == this.customColumnChooserDialog || this.customColumnChooserDialog.IsDisposed)
            {
                this.customColumnChooserDialog = new RLZY.CustomColumnChooser();
                this.customColumnChooserDialog.Owner = this;
                this.customColumnChooserDialog.Grid = this.ultraGrid1;
            }

            this.customColumnChooserDialog.Show();
        }
        //修改列布局
        private void btnDisplayLayout_Click(object sender, EventArgs e)
        {
            ultraGrid1.DisplayLayout.Bands[0].PerformAutoResizeColumns(true, PerformAutoSizeType.VisibleRows);
        }
        //隐藏列
        private void ultraGrid1_InitializeLayout(object sender, InitializeLayoutEventArgs e)
        {
            e.Layout.Bands[0].Columns[0].Hidden = true;
        }

        private void cmbBZ_SelectedIndexChanged(object sender, EventArgs e)
        {
            
        }
        
       
        
        //按回车键跳转

        protected override bool ProcessDialogKey(Keys keyData)
        {
            if (keyData == Keys.Enter && !(ActiveControl is Button))
            {//碰到Button时不跳转到下一个控件，而是执行Button的单击事件
                keyData = Keys.Tab;
            }
            return base.ProcessDialogKey(keyData);
        }

       
        
        public string VarDat { get; set; }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                this.btn四金分类.Enabled = false;
                others.SJ sj = new SJ();
                sj.DropGZ1();
               sj.cal(cmbBM.Text .Trim (),this.dtpDate.Text );
               DataSet ds = sj.GetListsijinfenlei();
               this.BindData(ds);
               sj.DropGZ1();
               this.btn四金分类.Enabled = true;
                
            }
            catch (Exception ex) { LOG.Error(ex); }
           
        }

        /// <summary>
        /// Toggles the buttons used for exporting the grid data.
        /// </summary>
        /// <param name="enabled">Boolean, used to signify whether or not to enable/disable the export buttons.</param>
        private void ExportToggle(bool enabled)
        {
            btn2Excel.Enabled = enabled;
            
        }

        private void btn2Excel_Click(object sender, EventArgs e)
        {
            DateTime dt = DateTime.Now;
            string filename = "四金统计_导出" + dt.ToFileTime().ToString();//127756416859912816
            StringBuilder strSql = new StringBuilder();
            strSql.Append("D:\\");
            strSql.Append(filename);
            strSql.Append(".xls");
            string path = strSql.ToString();  
            Process[] processlist = Process.GetProcesses();

            // Iterating through the running processses, and Killing the process that is holding your previosly opened Excel file.
            foreach (Process runningProcess in processlist)
            {
                if (runningProcess.MainWindowTitle.Contains(path))
                    runningProcess.Kill();
            }

            ExportToggle(false);
            try
            {
                ultraGridExcelExporter1.ExportAsync(ultraGrid1, path);
                //System.Diagnostics.Process.Start("explorer.exe", path);
                ExportToggle(true);
                //Msg.DisplayMsg("", "导出文件'" + path + "'", 1200);
                Msg.DisplayMsg("提示 ", "导出文件在：'" + path + "'", 6000);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                ExportToggle(true);
            }
        }



        public string LastDate { get; set; }

        private void FormNT_Show_FormClosing(object sender, FormClosingEventArgs e)
        {
            others.RYFB fb = new RYFB();
            fb.DropGZ1();
        }

        private void btn四金合并_Click(object sender, EventArgs e)
        {
            try
            {
                this.btn四金合并.Enabled = false;
                others.SJ sj = new SJ();
                sj.DropGZ1();
                sj.calHB(cmbBM.Text.Trim(), this.dtpDate.Text);
                DataSet ds = sj.GetListHB();
                this.BindData(ds);
                sj.DropGZ1();
                this.btn四金合并.Enabled = true;
            }
            catch (Exception ex) { LOG.Error(ex); }
        }



        private void btn打印四金通知书_Click(object sender, EventArgs e)
        {
            GetSum();
            ToExcel();
            

        }

        private void GetSum()
        {
            String bm=this.cmbBM.Text .Trim ();
            String date=this.dtpDate.Text.Substring(0,7);
            others.SJ sj = new SJ();
            DataSet dsylj = sj.GetSumYLJ(bm, date);
            if (dsylj.Tables[0].Rows.Count > 0)
            {
                this.x1 = Convert.ToDecimal(dsylj.Tables[0].Rows[0]["扣养老金"].ToString());
                this.x11 = Convert.ToInt32(dsylj.Tables[0].Rows[0]["人数"].ToString());
            }
            else
            {
                this.x1 = 0m;
                this.x11 = 0;
            }
            this.x1 = Convert.ToDecimal(Convert.ToDouble(this.x1) * 3.5);
            DataSet dssybx = sj.GetSumSYBX(bm, date);
            if (dssybx.Tables[0].Rows.Count > 0)
            {
                this.x2 = Convert.ToDecimal(dssybx.Tables[0].Rows[0]["扣失业保险"].ToString());
                this.x12 = Convert.ToInt32(dssybx.Tables[0].Rows[0]["人数"].ToString());
            }
            
            else
            {
                this.x2 = 0m;
                this.x12 = 0;
            }
            this.x2 = Convert.ToDecimal(Convert.ToDouble(this.x2) * 3);
            DataSet dsgjj = sj.GetSumGJJ(bm, date);
            if (dssybx.Tables[0].Rows.Count > 0)
            {
                this.x3 = Convert.ToDecimal(dsgjj.Tables[0].Rows[0]["扣公积金"].ToString());
                this.x13 = Convert.ToInt32(dsgjj.Tables[0].Rows[0]["人数"].ToString());
            }
            else
            {
                this.x3 = 0m;
                this.x13 = 0;
            }
            this.x3 = Convert.ToDecimal(Convert.ToDouble(this.x3) * 2);
            DataSet dsylbx = sj.GetSumYLBX(bm, date);
            if (dsylbx.Tables[0].Rows.Count > 0)
            {
                this.x4 = Convert.ToDecimal(dsylbx.Tables[0].Rows[0]["扣医疗保险"].ToString());
                this.x14 = Convert.ToInt32(dsylbx.Tables[0].Rows[0]["人数"].ToString());
            }
            else
            {
                this.x4 = 0m;
                this.x14 = 0;
            }
            this.x4 = new decimal(Convert.ToDouble(this.x4) * 4.5);
            DataSet dsrs = sj.GetSumRS(bm, date);
            if (dsylbx.Tables[0].Rows.Count > 0)
            {

                this.x15 = Convert.ToInt32(dsrs.Tables[0].Rows[0]["人数"].ToString());
            }
            else
            {
               
                this.x15 = 0;
            }
            this.x5 = new decimal(Math.Round((double)this.x15 * 0.016 * 2621.0, 2));
            this.x1 = Math.Round(this.x1, 2);
            this.x2 = Math.Round(this.x2, 2);
            this.x3 = Math.Round(this.x3, 2);
            this.x4 = Math.Round(this.x4, 2);
            this.x6 = decimal.Add(decimal.Add(decimal.Add(decimal.Add(this.x1, this.x2), this.x3), this.x4), this.x5);
            this.x6 = Math.Round(this.x6, 2);
        }
        private void ToExcel()
        {
            try
            {
                //模板文件
                string TempletFileName = "SJTZS.xls";
                //导出文件
                string ReportFileName = "C:\\SJTZS_New.xls";
                FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);

                ISheet ws = hssfworkbook.GetSheet("Sheet1");
                //添加或修改WorkSheet里的数据


                #region

                ws.GetRow(3).GetCell(0).SetCellValue(this.cmbBM.Text.Trim());//部门
                ws.GetRow(4).GetCell(1).SetCellValue(this.dtpDate.Text.Substring(0, 4));//年份
                ws.GetRow(4).GetCell(3).SetCellValue(this.dtpDate.Text.Substring(5, 2));//月份
                ws.GetRow(10).GetCell(2).SetCellValue(this.dtpDate.Text.Substring(0, 4));//年份
                ws.GetRow(10).GetCell(4).SetCellValue(this.dtpDate.Text.Substring(5, 2));//月份
                ws.GetRow(14).GetCell(8).SetCellValue(this.dtpDate.Value.ToString("yyyy-MM-dd HH:mm"));//时间
                ws.GetRow(5).GetCell(0).SetCellValue(x1.ToString());//养老金
                ws.GetRow(5).GetCell(5).SetCellValue(x11.ToString());
                ws.GetRow(6).GetCell(0).SetCellValue(x2.ToString());//失业保险
                ws.GetRow(6).GetCell(5).SetCellValue(x12.ToString());
                ws.GetRow(7).GetCell(0).SetCellValue(x3.ToString());//公积金
                ws.GetRow(7).GetCell(5).SetCellValue(x13.ToString());
                ws.GetRow(8).GetCell(0).SetCellValue(x4.ToString());//医疗保险
                ws.GetRow(8).GetCell(5).SetCellValue(x14.ToString());
                ws.GetRow(9).GetCell(0).SetCellValue(x5.ToString());//工伤保险
                ws.GetRow(9).GetCell(5).SetCellValue(x15.ToString());

                ws.GetRow(9).GetCell(9).SetCellValue(x6.ToString()); //总额


                #endregion
                ws.ForceFormulaRecalculation = true;

                using (FileStream filess = File.OpenWrite(ReportFileName))
                {
                    hssfworkbook.Write(filess);
                }
                System.Diagnostics.Process.Start("explorer.exe", @"C:\SJTZS_New.xls");
            }
            catch (Exception ex) { LOG.Error(ex); }

            //System.Diagnostics.Process.Start(Application.StartupPath + "C:\\RLZY_Prn\\SJTZS_Tmp_New.xls");
        }

        public decimal x1 { get; set; }

        public int x11 { get; set; }

        public int x12 { get; set; }

        public decimal x2 { get; set; }

        public decimal x3 { get; set; }

        public int x13 { get; set; }

        public int x14 { get; set; }

        public decimal x4 { get; set; }

        public int x15 { get; set; }

        public decimal x6 { get; set; }

        public decimal x5 { get; set; }
       
    }
}
